/* public static DataTable ConvertCSVtoDataTable(string strFilePath)
{
StreamReader sr = new StreamReader(strFilePath);
string[] headers = sr.ReadLine().Split(',');
DataTable dt = new DataTable();
foreach (string header in headers)
{
dt.Columns.Add(header);
}
while (!sr.EndOfStream)
{
string[] rows = Regex.Split(sr.ReadLine(), ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
DataRow dr = dt.NewRow();
for (int i = 0; i < headers.Length; i++)
{
dr[i] = rows[i];
}
dt.Rows.Add(dr);
}
return dt;
}
*/
public static void createXLS(DataTable dt) {
FileStream stream = new FileStream("YAPILANDIRMALAR.xls", FileMode.OpenOrCreate);
ExcelWriter writer = new ExcelWriter(stream);
writer.BeginWrite();
for (int i = 0; i < dt.Columns.Count; i++)
{
string name = dt.Columns[i].ColumnName.ToString();
writer.WriteCell(0, i, name);
}
/* */
for (int r = 0; r < dt.Rows.Count; r++)
{
//string TempStr = HeadStr;
//Sb.Append("{");
for (int c = 0; c < dt.Columns.Count; c++)
{
writer.WriteCell(r+1, c, dt.Rows[r][c].ToString());
// TempStr = TempStr.Replace("<br>", Environment.NewLine).Replace(Dt.Columns[j] + j.ToString() + "¾", Dt.Rows[r][c].ToString());
}
//Sb.Append(TempStr + "},");
}
writer.EndWrite();
stream.Close();
}
public void Write(IEnumerable <Dictionary <string, object> > datas, Dictionary <string, object> fields, string path)
{
FileStream stream = new FileStream(path, System.IO.FileMode.OpenOrCreate);
ExcelWriter xlwriter = new ExcelWriter(stream);
xlwriter.BeginWrite();
foreach (var field in fields.Select((value, index) => new { index = index, value = value }))
{
var obj = field.value.Key;
xlwriter.WriteCell(0, field.index, (field.value.Value).ToString());
foreach (var data in datas.Select((value, index) => new { value = value, index = index + 1 }))
{
if (data.value.ContainsKey(obj))
{
if (data.value[obj] != null)
{
xlwriter.WriteCell(data.index, field.index, data.value[obj].ToString());
}
}
}
}
xlwriter.EndWrite();
stream.Close();
}
public static void ToExcel(string excelSavePath, DataTable sourceTable)
{
int cols = sourceTable.Columns.Count;
int rows = sourceTable.Rows.Count;
ExcelWriter excel = new ExcelWriter(excelSavePath);
excel.BeginWrite();
//标题栏
for (int c = 0; c < cols; c++)
{
excel.WriteString((short)0, (short)c, sourceTable.Columns[c].ToString());
}
//数据
string productNumber = null;
int rowIndex = 0;
for (int r = 0; r < rows; r++)
{
if (productNumber != sourceTable.Rows[r][0].ToString())
{
productNumber = sourceTable.Rows[r][0].ToString();
excel.WriteString((short)(r + 1), (short)0, productNumber);
}
for (int c = 1; c < cols; c++)
{
excel.WriteString((short)(r + 1), (short)c, sourceTable.Rows[r][c].ToString());
}
}
excel.EndWrite();
}
public void Write <T>(IEnumerable <T> datas, Dictionary <string, object> fields, string path)
{
FileStream stream = new FileStream(path, System.IO.FileMode.OpenOrCreate);
ExcelWriter xlwriter = new ExcelWriter(stream);
xlwriter.BeginWrite();
if (fields == null)
{
fields = new Dictionary <string, object>();
foreach (var item in datas.FirstOrDefault().GetType().GetProperties())
{
fields.Add(item.Name, item.Name);
}
}
foreach (var field in fields.Select((value, index) => new { index = index, value = value }))
{
var obj = field.value.Key;
xlwriter.WriteCell(0, field.index, field.value.Value.ToString());
foreach (var data in datas.Select((value, index) => new { value = value, index = index + 1 }))
{
if (typeof(T).IsClass)
{
var key = data.value.GetType().GetProperty(field.value.Key).GetValue(data.value, null);
if (key != null)
{
var value = key.ToString();
xlwriter.WriteCell(data.index, field.index, value);
}
}
}
}
xlwriter.EndWrite();
stream.Close();
}
private PaymentTransaction paymentViaBlock(byte[] file)
{
//gach no theo lo
Company currentCom = ((EInvoiceContext)FXContext.Current).CurrentCompany;
PaymentTransaction mTran = new PaymentTransaction();
mTran.Data = file;
mTran.ComID = currentCom.id;
mTran.id = Guid.NewGuid();
mTran.AccountName = HttpContext.User.Identity.Name;
mTran.Status = PaymentTransactionStatus.NewUpload;
string message = "";
IPaymentExcel paymentExcelSrv = IoC.Resolve <IPaymentExcel>();
List <string> lstFkey = paymentExcelSrv.GetFkey(file, mTran.id.ToString(), out message);
if (lstFkey == null)
{
mTran.Messages = message;
mTran.Status = PaymentTransactionStatus.Failed;
return(mTran);
}
List <IInvoice> invLst = new List <IInvoice>();
PublishInvoice pub = DefaultPublishInvoice(currentCom.id);
IInvoiceService invSrv = InvServiceFactory.GetService(pub.InvPattern, currentCom.id);
int count = lstFkey.Count();
log.Info("paymentViaBlock get Inv");
List <IInvoice> foundLst = new List <IInvoice>();
foundLst = (List <IInvoice>)invSrv.GetByFkey(currentCom.id, lstFkey.ToArray());
log.Info("paymentViaBlock loc ket qua");
List <string> foundFkey = foundLst.Select(inv => inv.Fkey).ToList();
//khong ton tai
lstFkey.RemoveAll(inv => foundFkey.Contains(inv));
//đã gạch nợ rồi
List <IInvoice> updateInv = foundLst.GetRange(0, foundLst.Count);
updateInv.RemoveAll(inv => inv.PaymentStatus == Payment.Paid); //cần gạch nợ
foundLst.RemoveAll(inv => inv.PaymentStatus == Payment.Unpaid); //đã gạch nợ rồi.
List <string> unpaiedLst = updateInv.Select(inv => inv.Fkey).ToList();
List <string> paiedList = foundLst.Select(inv => inv.Fkey).ToList();
//ko ton tai lstFkey
//da gach no paiedList
//can gach no unpaiedLst
//ghi file excel ket qua
MemoryStream stream;
ExcelWriter writer;
if (lstFkey.Count > 0 || paiedList.Count > 0)
{
stream = new MemoryStream();
writer = new ExcelWriter(stream);
writer.BeginWrite();
int i = 0;
for (; i < lstFkey.Count; i++)
{
string s = lstFkey.ElementAt(i);
writer.WriteCell(i, 0, s);
writer.WriteCell(i, 1, "Khong ton tai");
}
for (int j = 0; j < paiedList.Count; j++)
{
string s = paiedList.ElementAt(j);
i++;
writer.WriteCell(i, 0, s);
writer.WriteCell(i, 1, "Da thanh toan tu truoc");
}
writer.EndWrite();
mTran.FailResult = stream.ToArray();
stream.Close();
}
//thuc hien gach no va ghi note moi
int[] ids = updateInv.Select(inv => inv.id).ToArray();
string noteAppends = " || Thực hiện gạch nợ: Người gạch nợ: " + HttpContext.User.Identity.Name + " Ngày gạch nợ: " + DateTime.Now.ToString();
log.Info("paymentViaBlock gach no");
if (invSrv.ConfirmPayment(ids, noteAppends))
//if (invSrv.ConfirmPayment(updateInv))
{
//thanh cong het
//tao file ket qua thanh cong
log.Info("paymentViaBlock ghi file thanh cong");
stream = new MemoryStream();
writer = new ExcelWriter(stream);
writer.BeginWrite();
for (int j = 0; j < updateInv.Count; j++)
{
string s = updateInv.ElementAt(j).Fkey;
writer.WriteCell(j, 0, s);
}
writer.EndWrite();
mTran.CompleteResult = stream.ToArray();
stream.Close();
if (lstFkey.Count == 0)
{
mTran.Status = PaymentTransactionStatus.Completed;
}
else
{
mTran.Status = PaymentTransactionStatus.NotComplete;
}
log.Info("Change Payment Status Einvoices by: " + HttpContext.User.Identity.Name + " Info-- GUID: " + mTran.id.ToString() + "Tổng số: " + (count) + ", gạch nợ thành công " + updateInv.Count + ", không tồn tại: " + lstFkey.Count + ", đã gạch nợ trước: " + paiedList.Count + " hóa đơn");
mTran.Messages = "Tổng số: " + (count) + ", gạch nợ thành công " + updateInv.Count + ", không tồn tại: " + lstFkey.Count + ", đã gạch nợ trước: " + paiedList.Count + " hóa đơn";
//thuc hien deliveriy
IDeliver _deliver = IoC.Resolve <IDeliver>();
_deliver.Deliver(updateInv.ToArray(), currentCom);
log.Info("paymentViaBlock end " + mTran.id);
return(mTran);
}
mTran.Status = PaymentTransactionStatus.Failed;
log.Info("Change Payment Status Einvoices by: " + HttpContext.User.Identity.Name + " Info-- GUID: " + mTran.id.ToString() + "Tổng số: " + (count) + " hóa đơn, gạch nợ fail " + updateInv.Count + ", không tồn tại: " + lstFkey.Count + ", đã gạch nợ trước: " + paiedList.Count + " hóa đơn");
if (count == 0)
{
mTran.Messages = "File không đúng mẫu hoặc không chứa dữ liệu";
}
else
{
mTran.Messages = "Tổng số: " + (count) + " hóa đơn, gạch nợ fail " + updateInv.Count + ", không tồn tại: " + lstFkey.Count + ", đã gạch nợ trước: " + paiedList.Count + " hóa đơn";
}
return(mTran);
}
void tsbExportToExcelClick(object sender, EventArgs e)
{
try {
SaveFileDialog saveXLS = new SaveFileDialog();
saveXLS.FileName = _currentProject.Name;
saveXLS.DefaultExt = "xls";
saveXLS.Filter = "Microsoft Office Excel Workbook |(*.xls*)";
saveXLS.CheckFileExists = false;
saveXLS.InitialDirectory = projectsPath;
if (saveXLS.ShowDialog() == DialogResult.OK)
{
int totalRows = 0;
FileStream stream = new FileStream(saveXLS.FileName, FileMode.OpenOrCreate);
ExcelWriter writer = new ExcelWriter(stream);
writer.BeginWrite();
writer.WriteCell(0, 0, "Station ID");
writer.WriteCell(0, 1, "Camera Manufacturer");
writer.WriteCell(0, 2, "Camera Model");
writer.WriteCell(0, 3, "Image Name");
writer.WriteCell(0, 4, "Date");
writer.WriteCell(0, 5, "Time");
writer.WriteCell(0, 6, "Pressure (inHg)");
writer.WriteCell(0, 7, "Temperature (Celsius)");
writer.WriteCell(0, 8, "Species");
writer.WriteCell(0, 9, "Count");
foreach (Station st in _currentProject.StationsList)
{
foreach (Sample smp in st.SamplesList)
{
totalRows++;
writer.WriteCell(totalRows, 0, st.StationID);
writer.WriteCell(totalRows, 1, smp.CameraManufacturer);
writer.WriteCell(totalRows, 2, smp.CameraModel);
writer.WriteCell(totalRows, 3, smp.ImageName);
writer.WriteCell(totalRows, 4, smp.DateTime.ToShortDateString());
writer.WriteCell(totalRows, 5, smp.DateTime.ToShortTimeString());
writer.WriteCell(totalRows, 6, (double)smp.OCR_Observations_list[0].Value); //Press
writer.WriteCell(totalRows, 7, (int)smp.OCR_Observations_list[1].Value); //Temp
SpeciesObservation spc0 = (SpeciesObservation)smp.Species_Observations_list[0];
writer.WriteCell(totalRows, 8, (string)spc0.Value); //Species0
writer.WriteCell(totalRows, 9, (int)spc0.Count); //Count0
//more than 1 specie observation, duplicate the row changing that value
if (smp.OCR_Observations_list.Count > 3)
{
for (int j = 3; j < smp.OCR_Observations_list.Count; j++)
{
totalRows++;
writer.WriteCell(totalRows, 0, st.StationID);
writer.WriteCell(totalRows, 1, smp.CameraManufacturer);
writer.WriteCell(totalRows, 2, smp.CameraModel);
writer.WriteCell(totalRows, 3, smp.ImageName);
writer.WriteCell(totalRows, 4, smp.DateTime.ToShortDateString());
writer.WriteCell(totalRows, 5, smp.DateTime.ToShortTimeString());
writer.WriteCell(totalRows, 6, (double)smp.OCR_Observations_list[0].Value); //Press
writer.WriteCell(totalRows, 7, (int)smp.OCR_Observations_list[1].Value); //Temp
SpeciesObservation spcN = (SpeciesObservation)smp.Species_Observations_list[j];
writer.WriteCell(totalRows, 8, (string)spcN.Value); //Species0
writer.WriteCell(totalRows, 9, (int)spcN.Count); //Count0
}
}
}
}
writer.EndWrite();
stream.Close();
}
} catch (Exception ex) {
throw ex;
}
}