· 6 years ago · May 26, 2019, 01:26 PM
1using System;
2using System.Collections.Generic;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Linq;
7using System.Text;
8using System.Threading.Tasks;
9using System.Windows.Forms;
10using Oracle.ManagedDataAccess.Client;
11using UIS.Employee;
12
13namespace UIS.Report
14{
15 public partial class SS : Form
16 {
17 string vssorclcon = System.Configuration.ConfigurationManager.ConnectionStrings["orclcon"].ConnectionString;
18 //string vssorclcon = "DATA SOURCE=MrStupid:1521/XE;PERSIST SECURITY INFO=True;USER ID=UIS; Password=uis2019";
19
20 TextBox post, name, contact_num;
21 RichTextBox msg;
22 List<TextBox> generatedTextboxes = new List<TextBox>();
23 List<RichTextBox> generatedRichTextboxes = new List<RichTextBox>();
24 Bitmap memoryImage;
25 int fcount;
26 DataSet vssorcldt;
27 BindingSource vssorclbindingSource;
28
29 public SS()
30 {
31 InitializeComponent();
32 this.ActiveControl = textBoxssunitnumberval;
33 textBoxssunitnumberval.Focus();
34 //ssViewClear();
35 //richTextBoxssemployer.SelectionAlignment = HorizontalAlignment.Center;
36
37 dateTimePickerssfinancialyear.CustomFormat = "MMM-yyyy";
38 }
39
40 private void SS_Load(object sender, EventArgs e)
41 {
42 textBoxssfinancialyearval.Text = dateTimePickerssfinancialyear.Value.ToString("MMM - yyyy").ToUpper();
43
44
45 dataGridViewchqcoll.BorderStyle = BorderStyle.None;
46 dataGridViewchqcoll.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(238, 239, 249);
47 //dataGridViewchqcoll.CellBorderStyle = DataGridViewCellBorderStyle.SingleHorizontal;
48 //dataGridViewchqcoll.CellBorderStyle = DataGridViewCellBorderStyle.SingleVertical;
49 dataGridViewchqcoll.DefaultCellStyle.SelectionBackColor = Color.LightGreen; //DarkTurquoise;
50 dataGridViewchqcoll.DefaultCellStyle.SelectionForeColor = Color.Black;
51 dataGridViewchqcoll.BackgroundColor = Color.White;
52
53 dataGridViewchqcoll.EnableHeadersVisualStyles = false;
54 dataGridViewchqcoll.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Raised;
55 //dataGridViewchqcoll.ColumnHeadersDefaultCellStyle.BackColor = Color.FromArgb(20, 25, 72);
56 //dataGridViewchqcoll.ColumnHeadersDefaultCellStyle.ForeColor = Color.LightBlue;
57 dataGridViewchqcoll.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
58
59 dataGridViewsal.BorderStyle = BorderStyle.None;
60 dataGridViewsal.EnableHeadersVisualStyles = false;
61 dataGridViewsal.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Raised;
62 dataGridViewsal.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
63 dataGridViewsal.DefaultCellStyle.SelectionBackColor = Color.LightGreen;
64 dataGridViewsal.DefaultCellStyle.SelectionForeColor = Color.Black;
65 dataGridViewsal.BackgroundColor = Color.White;
66
67 dataGridViewbill.BorderStyle = BorderStyle.None;
68 dataGridViewbill.EnableHeadersVisualStyles = false;
69 dataGridViewbill.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Raised;
70 dataGridViewbill.DefaultCellStyle.SelectionBackColor = Color.LightGreen;
71 dataGridViewbill.DefaultCellStyle.SelectionForeColor = Color.Black;
72 dataGridViewbill.BackgroundColor = Color.White;
73 //dataGridViewbill.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
74
75 dataGridViewemployeer.BorderStyle = BorderStyle.None;
76 dataGridViewemployeer.EnableHeadersVisualStyles = false;
77 dataGridViewemployeer.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Raised;
78 dataGridViewemployeer.DefaultCellStyle.SelectionBackColor = Color.LightGreen;
79 dataGridViewemployeer.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
80 dataGridViewemployeer.DefaultCellStyle.SelectionForeColor = Color.Black;
81 dataGridViewemployeer.BackgroundColor = Color.White;
82
83
84 //Cheque Datagridview
85 this.dataGridViewchqcoll.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing;
86 this.dataGridViewchqcoll.ColumnHeadersHeight = this.dataGridViewchqcoll.ColumnHeadersHeight * 2;
87 this.dataGridViewchqcoll.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter;
88 this.dataGridViewchqcoll.CellPainting += new DataGridViewCellPaintingEventHandler(dataGridViewchqcoll_CellPainting);
89 this.dataGridViewchqcoll.Paint += new PaintEventHandler(dataGridViewchqcoll_Paint);
90 this.dataGridViewchqcoll.Scroll += new ScrollEventHandler(dataGridViewchqcoll_Scroll);
91 this.dataGridViewchqcoll.ColumnWidthChanged += new DataGridViewColumnEventHandler(dataGridViewchqcoll_ColumnWidthChanged);
92
93 //Employeer Datagridview
94 this.dataGridViewemployeer.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing;
95 this.dataGridViewemployeer.ColumnHeadersHeight = this.dataGridViewchqcoll.ColumnHeadersHeight * 1;
96 this.dataGridViewemployeer.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter;
97 this.dataGridViewemployeer.CellPainting += new DataGridViewCellPaintingEventHandler(dataGridViewemployeer_CellPainting);
98 this.dataGridViewemployeer.Paint += new PaintEventHandler(dataGridViewemployeer_Paint);
99 this.dataGridViewemployeer.Scroll += new ScrollEventHandler(dataGridViewemployeer_Scroll);
100 this.dataGridViewemployeer.ColumnWidthChanged += new DataGridViewColumnEventHandler(dataGridViewemployeer_ColumnWidthChanged);
101 }
102
103 private void dataGridViewchqcoll_ColumnWidthChanged(object sender, DataGridViewColumnEventArgs e)
104 {
105 Rectangle rc = dataGridViewchqcoll.DisplayRectangle;
106 rc.Height = dataGridViewchqcoll.ColumnHeadersHeight / 2;
107 dataGridViewchqcoll.Invalidate(rc);
108 }
109
110 private void dataGridViewchqcoll_Scroll(object sender, ScrollEventArgs e)
111 {
112 Rectangle rc = dataGridViewchqcoll.DisplayRectangle;
113 rc.Height = dataGridViewchqcoll.ColumnHeadersHeight / 2;
114 dataGridViewchqcoll.Invalidate(rc);
115 }
116
117 private void dataGridViewchqcoll_Paint(object sender, PaintEventArgs e)
118 {
119 //Bill Deatils
120 string[] col1 = { "BILL DETAILS" };
121 for (int j = 1; j < 3;)
122 {
123 Rectangle r1 = this.dataGridViewchqcoll.GetCellDisplayRectangle(j, -1, true);
124 int w2 = this.dataGridViewchqcoll.GetCellDisplayRectangle(j + 1, -1, true).Width;
125 r1.X += 1;
126 r1.Y += 1;
127 r1.Width = r1.Width + w2 - 2;
128 r1.Height = r1.Height / 2 - 2;
129 e.Graphics.FillRectangle(new SolidBrush(this.dataGridViewchqcoll.ColumnHeadersDefaultCellStyle.BackColor), r1);
130 StringFormat format = new StringFormat();
131 format.Alignment = StringAlignment.Center;
132 format.LineAlignment = StringAlignment.Center;
133 e.Graphics.DrawString(col1[j / 2],
134 this.dataGridViewchqcoll.ColumnHeadersDefaultCellStyle.Font,
135 new SolidBrush(this.dataGridViewchqcoll.ColumnHeadersDefaultCellStyle.ForeColor),
136 r1,
137 format);
138 j += 2;
139 }
140
141 //Cheque Details
142 string[] col2 = { "CHEQUE DETAILS" };
143 for (int j = 3; j < 6;)
144 {
145 Rectangle r1 = this.dataGridViewchqcoll.GetCellDisplayRectangle(j, -1, true); //Start Of The Cell
146 int w2 = this.dataGridViewchqcoll.GetCellDisplayRectangle(j + 1, -1, true).Width; //2nd Column
147 int w3 = this.dataGridViewchqcoll.GetCellDisplayRectangle(j + 2, -1, true).Width; //3rd Column. If we want to Merge 4 Coulmn then need to write one more below w4
148 r1.X += 1;
149 r1.Y += 1;
150 r1.Width = r1.Width + w2 + w3 - 2;
151 r1.Height = r1.Height / 2 - 2;
152 e.Graphics.FillRectangle(new SolidBrush(this.dataGridViewchqcoll.ColumnHeadersDefaultCellStyle.BackColor), r1);
153 StringFormat format = new StringFormat();
154 format.Alignment = StringAlignment.Center;
155 format.LineAlignment = StringAlignment.Center;
156 e.Graphics.DrawString(col2[0],
157 this.dataGridViewchqcoll.ColumnHeadersDefaultCellStyle.Font,
158 new SolidBrush(this.dataGridViewchqcoll.ColumnHeadersDefaultCellStyle.ForeColor),
159 r1,
160 format);
161 j += 3;
162 }
163 }
164
165 private void dataGridViewchqcoll_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
166 {
167 if (e.RowIndex == -1 && e.ColumnIndex > -1)
168 {
169 Rectangle r2 = e.CellBounds;
170 r2.Y += e.CellBounds.Height / 2;
171 r2.Height = e.CellBounds.Height / 2;
172 e.PaintBackground(r2, true);
173 e.PaintContent(r2);
174 e.Handled = true;
175 }
176 }
177
178 private void fetchChequeCollectionDetails(string clientid, string bmdate)
179 {
180 try
181 {
182 OracleConnection con = new OracleConnection(vssorclcon);
183 con.Open();
184
185 OracleDataAdapter vsscloclcmd = new OracleDataAdapter("select BUSINESS_MONTH,CLIENT_BILL_NO,CLIENT_BILL_AMOUNT,to_char(CLIENT_CHEQUE_RECV_DATE,'DD-MON-YYYY') as CLIENT_CHEQUE_RECV_DATE,CLIENT_CHEQUE_NO,CLIENT_CHEQUE_RECV_AMOUNT from UIS_CLIENT_CHEQUE where upper(BUSINESS_MONTH) in (" + bmdate.ToUpper() + ") and upper(client_id) in ('" + clientid.ToUpper() + "') order by to_date(BUSINESS_MONTH,'MM-YYYY') desc", con);
186
187 vssorcldt = new DataSet();
188
189 vsscloclcmd.Fill(vssorcldt, "UIS_CLIENT_CHEQUE");
190 dataGridViewchqcoll.DataSource = vssorcldt.Tables[0];
191
192 if(dataGridViewchqcoll.Rows.Count == 0)
193 {
194 MessageBox.Show("For Last 12 Month No Cheque Details Are Available For This Client!!", "Alert", MessageBoxButtons.OK);
195 con.Close();
196 vssorcldt.Clear();
197 //dataGridViewchqcoll.ClearSelection();
198 }
199
200 con.Close();
201
202 }
203 catch (OracleException ex)
204 {
205 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
206 }
207 }
208
209 //Calculate Business Month
210 public DateTime toDatePeriod(int y, int m, int mon)
211 {
212 //int days = DateTime.DaysInMonth(y, m);
213 DateTime date = new DateTime(y, m, 1);
214 if (mon == 0)
215 {
216 return date.AddMonths(mon).AddDays(mon);
217 }
218 else
219 {
220 return date.AddMonths(1 + mon).AddDays(mon);
221 }
222 }
223
224 //Fetch Client Rate And Bill Details
225 private void fetchClientRateBillDetails(string clientid)
226 {
227 try
228 {
229 OracleConnection con = new OracleConnection(vssorclcon);
230 con.Open();
231
232 OracleCommand vsscrateoclcmd = new OracleCommand("select CLIENT_RATE_OF_SV,CLIENT_RATE_OF_SG,CLIENT_RATE_OF_BO,CLIENT_RATE_OF_LM,CLIENT_RATE_OF_SW,CLIENT_RATE_OF_GARDENER,CLIENT_SERVICE_CHARGE,CLIENT_CGST,CLIENT_SGST,CLIENT_RATE_COMMENT from UIS_CLIENT_RATE where upper(client_id) ='" + clientid.ToUpper() + "' order by client_id asc", con);
233 OracleDataReader vsscratedbreader;
234 vsscratedbreader = vsscrateoclcmd.ExecuteReader();
235
236 OracleDataAdapter vssurateoclcmd = new OracleDataAdapter("select E_POST,E_UNIT_RATE from (" +
237 "select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('BOUNCER') group by E_POST,E_UNIT_RATE" +
238 " union all " +
239 "select 'BOUNCER',0 from dual where NOT EXISTS (select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('BOUNCER') group by E_POST,E_UNIT_RATE)" +
240 " union all " +
241 "select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('SUPERVISOR') group by E_POST,E_UNIT_RATE" +
242 " union all " +
243 "select 'SUPERVISOR',0 from dual where NOT EXISTS (select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('SUPERVISOR') group by E_POST,E_UNIT_RATE)" +
244 " union all " +
245 "select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('SECURITY GUARD') group by E_POST,E_UNIT_RATE" +
246 " union all " +
247 "select 'SECURITY GUARD',0 from dual where NOT EXISTS (select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('SECURITY GUARD') group by E_POST,E_UNIT_RATE)" +
248 " union all " +
249 "select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('LIFT MAN') group by E_POST,E_UNIT_RATE" +
250 " union all " +
251 "select 'LIFT MAN',0 from dual where NOT EXISTS (select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('LIFT MAN') group by E_POST,E_UNIT_RATE)" +
252 " union all " +
253 "select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('SWEEPER') group by E_POST,E_UNIT_RATE" +
254 " union all " +
255 "select 'SWEEPER',0 from dual where NOT EXISTS (select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('SWEEPER') group by E_POST,E_UNIT_RATE)" +
256 " union all " +
257 "select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('GARDENER') group by E_POST,E_UNIT_RATE" +
258 " union all " +
259 "select 'GARDENER',0 from dual where NOT EXISTS (select E_POST,E_UNIT_RATE from UIS_EMPLOYEE_RATE where upper(e_client_id)='" + clientid.ToUpper() + "' and upper(e_post) in ('GARDENER') group by E_POST,E_UNIT_RATE)) b", con);
260
261 vssorcldt = new DataSet();
262
263 if (vsscratedbreader.HasRows)
264 {
265 vssurateoclcmd.Fill(vssorcldt, "b");
266 dataGridViewsal.DataSource = vssorcldt.Tables[0];
267
268 OracleDataAdapter vssurateoclcmds = new OracleDataAdapter("select POST_RATE,CLIENT_SERVICE_CHARGE,CLIENT_CGST,CLIENT_SGST,CLIENT_RATE_COMMENT from (" +
269 "select CLIENT_RATE_OF_BO as POST_RATE,CLIENT_SERVICE_CHARGE,CLIENT_CGST,CLIENT_SGST,CLIENT_RATE_COMMENT from UIS_CLIENT_RATE where upper(client_id) ='" + clientid.ToUpper() + "'" +
270 " union all " +
271 "select CLIENT_RATE_OF_SV as POST_RATE,CLIENT_SERVICE_CHARGE,CLIENT_CGST,CLIENT_SGST,CLIENT_RATE_COMMENT from UIS_CLIENT_RATE where upper(client_id) ='" + clientid.ToUpper() + "'" +
272 " union all " +
273 "select CLIENT_RATE_OF_SG as POST_RATE,CLIENT_SERVICE_CHARGE,CLIENT_CGST,CLIENT_SGST,CLIENT_RATE_COMMENT from UIS_CLIENT_RATE where upper(client_id) ='" + clientid.ToUpper() + "'" +
274 " union all " +
275 "select CLIENT_RATE_OF_LM as POST_RATE,CLIENT_SERVICE_CHARGE,CLIENT_CGST,CLIENT_SGST,CLIENT_RATE_COMMENT from UIS_CLIENT_RATE where upper(client_id) ='" + clientid.ToUpper() + "'" +
276 " union all " +
277 "select CLIENT_RATE_OF_SW as POST_RATE,CLIENT_SERVICE_CHARGE,CLIENT_CGST,CLIENT_SGST,CLIENT_RATE_COMMENT from UIS_CLIENT_RATE where upper(client_id) ='" + clientid.ToUpper() + "'" +
278 " union all " +
279 "select CLIENT_RATE_OF_GARDENER as POST_RATE,CLIENT_SERVICE_CHARGE,CLIENT_CGST,CLIENT_SGST,CLIENT_RATE_COMMENT from UIS_CLIENT_RATE where upper(client_id) ='" + clientid.ToUpper() + "') a", con);
280 DataSet vssorcldts = new DataSet();
281 vssurateoclcmds.Fill(vssorcldts, "a");
282
283 dataGridViewbill.DataSource = vssorcldts.Tables[0];
284
285 con.Close();
286 }
287 else
288 {
289 MessageBox.Show("Client Does Not Exist In Client Rate Table!!", "Alert", MessageBoxButtons.OK);
290 con.Close();
291 dataGridViewsal.ClearSelection();
292 dataGridViewbill.ClearSelection();
293 }
294 con.Close();
295 }
296 catch (OracleException ex)
297 {
298 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
299 }
300 }
301
302 private void dataGridViewemployeer_ColumnWidthChanged(object sender, DataGridViewColumnEventArgs e)
303 {
304 Rectangle rc = dataGridViewemployeer.DisplayRectangle;
305 rc.Height = dataGridViewemployeer.ColumnHeadersHeight / 3;
306 dataGridViewemployeer.Invalidate(rc);
307 }
308
309 private void dataGridViewemployeer_Scroll(object sender, ScrollEventArgs e)
310 {
311 Rectangle rc = dataGridViewemployeer.DisplayRectangle;
312 rc.Height = dataGridViewemployeer.ColumnHeadersHeight / 3;
313 dataGridViewemployeer.Invalidate(rc);
314 }
315
316 private void dataGridViewemployeer_Paint(object sender, PaintEventArgs e)
317 {
318 //Cheque Details
319 string[] col2 = { "EMPLOYEERS" };
320 for (int j = 0; j < 3;)
321 {
322 Rectangle r1 = this.dataGridViewemployeer.GetCellDisplayRectangle(j, -1, true); //Start Of The Cell
323 int w2 = this.dataGridViewemployeer.GetCellDisplayRectangle(j + 1, -1, true).Width; //2nd Column
324 int w3 = this.dataGridViewemployeer.GetCellDisplayRectangle(j + 2, -1, true).Width; //3rd Column. If we want to Merge 4 Coulmn then need to write one more below w4
325 r1.X += 1;
326 r1.Y += 1;
327 r1.Width = r1.Width + w2 + w3 - 2;
328 r1.Height = r1.Height / 3 - 3;
329 e.Graphics.FillRectangle(new SolidBrush(this.dataGridViewemployeer.ColumnHeadersDefaultCellStyle.BackColor), r1);
330 StringFormat format = new StringFormat();
331 format.Alignment = StringAlignment.Center;
332 format.LineAlignment = StringAlignment.Center;
333 e.Graphics.DrawString(col2[0],
334 this.dataGridViewemployeer.ColumnHeadersDefaultCellStyle.Font,
335 new SolidBrush(this.dataGridViewemployeer.ColumnHeadersDefaultCellStyle.ForeColor),
336 r1,
337 format);
338 j += 3;
339 }
340 }
341
342 private void dataGridViewemployeer_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
343 {
344 if (e.RowIndex == -1 && e.ColumnIndex > -1)
345 {
346 Rectangle r2 = e.CellBounds;
347 r2.Y += e.CellBounds.Height / 2;
348 r2.Height = e.CellBounds.Height / 2;
349 e.PaintBackground(r2, true);
350 e.PaintContent(r2);
351 e.Handled = true;
352 }
353 }
354
355 //Fetch Employer Details
356 private void fetchEmployerDetails(string clientid)
357 {
358 try
359 {
360 OracleConnection con = new OracleConnection(vssorclcon);
361 con.Open();
362
363 OracleDataAdapter vsscloclcmd = new OracleDataAdapter("select POST,NAME,CONTACT from (" +
364 "select CLIENT_POST as POST,CLIENT_CONTACT_PERSON as NAME,CLIENT_CONTACT as CONTACT from uis_client where upper(client_id) ='" + clientid.ToUpper() + "'" +
365 " union all " +
366 "select CLIENT_POST1 as POST,CLIENT_EMERGENCY_PERSON1 as NAME,CLIENT_EMERGENCY_CONTACT1 as CONTACT from uis_client where upper(client_id) ='" + clientid.ToUpper() + "'" +
367 " union all " +
368 "select CLIENT_POST2 as POST,CLIENT_EMERGENCY_PERSON2 as NAME,CLIENT_EMERGENCY_CONTACT2 as CONTACT from uis_client where upper(client_id) ='" + clientid.ToUpper() + "'" +
369 " union all " +
370 "select CLIENT_POST3 as POST,CLIENT_EMERGENCY_PERSON3 as NAME,CLIENT_EMERGENCY_CONTACT3 as CONTACT from uis_client where upper(client_id) ='" + clientid.ToUpper() + "')a", con);
371 DataSet vssorcldts = new DataSet();
372 vsscloclcmd.Fill(vssorcldts, "a");
373
374 dataGridViewemployeer.DataSource = vssorcldts.Tables[0];
375
376 if(dataGridViewemployeer.Rows.Count == 0)
377 {
378 MessageBox.Show("Client Does Not Exist In Database!!", "Alert", MessageBoxButtons.OK);
379 vssorcldts.Clear();
380 con.Close();
381 }
382 con.Close();
383 }
384 catch (OracleException ex)
385 {
386 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
387 }
388 }
389
390 private void rundgv_Paint(object sender, PaintEventArgs e)
391 {
392 string[] col2 = { "EMPLOYEE" };
393 for (int j = 0; j < 3;)
394 {
395 Rectangle r1 = this.dataGridViewemployeer.GetCellDisplayRectangle(j, -1, true); //Start Of The Cell
396 int w2 = this.dataGridViewemployeer.GetCellDisplayRectangle(j + 1, -1, true).Width; //2nd Column
397 int w3 = this.dataGridViewemployeer.GetCellDisplayRectangle(j + 2, -1, true).Width; //3rd Column. If we want to Merge 4 Coulmn then need to write one more below w4
398 r1.X += 1;
399 r1.Y += 1;
400 r1.Width = r1.Width + w2 + w3 - 2;
401 r1.Height = r1.Height / 3 - 3;
402 e.Graphics.FillRectangle(new SolidBrush(this.dataGridViewemployeer.ColumnHeadersDefaultCellStyle.BackColor), r1);
403 StringFormat format = new StringFormat();
404 format.Alignment = StringAlignment.Center;
405 format.LineAlignment = StringAlignment.Center;
406 e.Graphics.DrawString(col2[0],
407 this.dataGridViewemployeer.ColumnHeadersDefaultCellStyle.Font,
408 new SolidBrush(this.dataGridViewemployeer.ColumnHeadersDefaultCellStyle.ForeColor),
409 r1,
410 format);
411 j += 3;
412 }
413 }
414
415 //Fetch Employee Details On RunTime
416 private void fetchEmployeeDetails(string clientid)
417 {
418 try
419 {
420 OracleConnection con = new OracleConnection(vssorclcon);
421 con.Open();
422
423 DataGridView rundgv = new DataGridView();
424
425 OracleDataAdapter vssempoclcmd = new OracleDataAdapter("select E_POST,E_NAME,E_CONTACT from UIS_EMPLOYEE where upper(e_client_id) ='" + clientid.ToUpper() + "' and upper(e_status)='A' order by E_ID asc", con);
426 DataSet vssorcldts = new DataSet();
427 vssempoclcmd.Fill(vssorcldts, "UIS_EMPLOYEE");
428
429 rundgv.DataSource = vssorcldts.Tables[0];
430 Controls.Add(rundgv);
431
432 if (rundgv.Rows.Count == 0)
433 {
434 MessageBox.Show("None Employee Working For '" + textBoxssunitnumberval.Text.ToUpper() + "' Client!!", "Alert", MessageBoxButtons.OK);
435 con.Close();
436 //rundgv.DataSource = null;
437
438 this.Controls.Remove(rundgv);
439 }
440 else
441 {
442 rundgv.Location = new Point(427, 578);
443 rundgv.Size = new Size(402, 165); //132
444
445 rundgv.AllowUserToAddRows = false;
446 rundgv.AllowUserToResizeRows = false;
447 rundgv.Anchor= AnchorStyles.None;
448 rundgv.ReadOnly = true;
449 rundgv.RowHeadersVisible = false;
450 rundgv.DefaultCellStyle.SelectionBackColor = Color.LightGreen;
451 rundgv.DefaultCellStyle.SelectionForeColor = Color.Black;
452 rundgv.BackgroundColor = Color.White;
453 rundgv.BorderStyle = BorderStyle.None;
454 rundgv.EnableHeadersVisualStyles = false;
455 rundgv.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Raised;
456 rundgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
457
458 rundgv.Columns[0].HeaderText = "POST";
459 rundgv.Columns[0].Width = 100;
460 rundgv.Columns[1].HeaderText = "NAME";
461 rundgv.Columns[1].Width = 100;
462 rundgv.Columns[2].HeaderText = "CONTACT NUMBER";
463 rundgv.Columns[2].Width = 150;
464
465 //Create Run Time Event
466 rundgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing;
467 rundgv.ColumnHeadersHeight = this.dataGridViewchqcoll.ColumnHeadersHeight * 1;
468 rundgv.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter;
469 rundgv.CellPainting += new DataGridViewCellPaintingEventHandler(dataGridViewemployeer_CellPainting);
470 rundgv.Paint += new PaintEventHandler(rundgv_Paint);
471 rundgv.Scroll += new ScrollEventHandler(dataGridViewemployeer_Scroll);
472 rundgv.ColumnWidthChanged += new DataGridViewColumnEventHandler(dataGridViewemployeer_ColumnWidthChanged);
473 con.Close();
474 }
475 con.Close();
476 }
477 catch (OracleException ex)
478 {
479 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
480 }
481 }
482
483 private void buttonssfindbtn_Click(object sender, EventArgs e)
484 {
485 if (string.IsNullOrEmpty(textBoxssunitnumberval.Text))
486 {
487 MessageBox.Show("Please Enter Client ID!!", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
488 }
489 else
490 {
491 try
492 {
493 OracleConnection con = new OracleConnection(vssorclcon);
494 con.Open();
495
496 OracleCommand vssocldataadp = new OracleCommand("select * from UIS_CLIENT where upper(CLIENT_ID) ='" + textBoxssunitnumberval.Text.ToUpper() + "'", con);
497 OracleDataReader vssdbreader;
498 vssdbreader = vssocldataadp.ExecuteReader();
499
500 if (vssdbreader.HasRows)
501 {
502 fetchClientRateBillDetails(textBoxssunitnumberval.Text.ToUpper()); // Each Time When I Click This Button All the previous data should get clearout from respective Datagrid View
503 fetchEmployerDetails(textBoxssunitnumberval.Text.ToUpper());
504 fetchEmployeeDetails(textBoxssunitnumberval.Text.ToUpper());
505
506 //Cheque Collection Details
507 try
508 {
509 DateTimePicker dateTimePickerdiffdatesss = new DateTimePicker();
510 dateTimePickerdiffdatesss.Value = DateTime.Now;
511 dateTimePickerdiffdatesss.CustomFormat = "MMM-yyyy";
512
513 //int y = DateTime.Now.Year;
514 //int m = DateTime.Now.Month;
515
516 int y = dateTimePickerssfinancialyear.Value.Year;
517 int m = dateTimePickerssfinancialyear.Value.Month;
518
519 int totalmon = 11;
520 string bdate = null;
521 for (int i = totalmon; i >= 0; i--)
522 {
523 dateTimePickerssfinancialyear.Value = toDatePeriod(y, m, -i);
524 //dateTimePickerdiffdate.Text = dateTimePickerdiffdate.Value.ToString();
525 bdate = bdate + ",'" + dateTimePickerssfinancialyear.Text + "'";
526 }
527 bdate = bdate.Substring(1);
528 fetchChequeCollectionDetails(textBoxssunitnumberval.Text.ToUpper(), bdate);
529
530 }
531 catch (SystemException ex)
532 {
533 MessageBox.Show(ex.Message + "(Error In Cheque Collection)", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
534 }
535 con.Close();
536 }
537 else
538 {
539 MessageBox.Show("Client ID Not Available In Database!!", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
540
541 dataGridViewchqcoll.ClearSelection(); //This way not working
542 //dataGridViewchqcoll.Ga;
543 dataGridViewemployeer.ClearSelection();
544 dataGridViewbill.ClearSelection();
545 dataGridViewsal.ClearSelection();
546 }
547 con.Close();
548 }
549 catch (OracleException ex)
550 {
551 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
552 //ssViewClear();
553 for (int i = 0; i < generatedTextboxes.Count; i++)
554 {
555 this.Controls.Remove(generatedTextboxes[i]);
556 }
557 for (int i = 0; i < generatedRichTextboxes.Count; i++)
558 {
559 this.Controls.Remove(generatedRichTextboxes[i]);
560 }
561 }
562 }
563 }
564 }
565}